CREATE VIEW [dbo].[vSoaGroupMemberSummaryMT]
WITH SCHEMABINDING
AS
    SELECT  CAST('MT-' + [mt].[MEMBER_TYPE] + ':' + [n].[ID] AS VARCHAR(50)) AS [GroupMemberId],
            CAST('MT-' + [mt].[MEMBER_TYPE] AS VARCHAR(50)) AS [GroupId],
            [n].[ID] AS [PartyId],
            [n].[FULL_NAME] AS [PartyName],
            CASE WHEN [n].[STATUS] = 'A' THEN CAST(1 AS BIT)
                 ELSE CAST(0 AS BIT)
            END AS [IsActive],
            [n].[JOIN_DATE] AS [JoinDate],
            [n].[PAID_THRU] AS [DropDate]
      FROM  [dbo].[Name] AS [n]
            INNER JOIN [dbo].[Member_Types] AS [mt] ON [n].[MEMBER_TYPE] = [mt].[MEMBER_TYPE]


GO
CREATE UNIQUE CLUSTERED INDEX [PK_vSoaGroupMemberSummaryMT] ON [dbo].[vSoaGroupMemberSummaryMT] ([GroupMemberId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaGroupMemberSummaryMT_GroupId] ON [dbo].[vSoaGroupMemberSummaryMT] ([GroupId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaGroupMemberSummaryMT_PartyId] ON [dbo].[vSoaGroupMemberSummaryMT] ([PartyId]) ON [PRIMARY]
GO